# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
import copy
import uuid

from itemadapter import ItemAdapter
from pymysql import cursors
from twisted.enterprise import adbapi


class FanqieResourcePipeline:

    # 初始化函数
    def __init__(self, db_pool):
        self.db_pool = db_pool

    # 从settings配置文件中读取参数
    @classmethod
    def from_settings(cls, settings):
        # 用一个db_params接收连接数据库的参数
        db_params = dict(
            host=settings['MYSQL_HOST'],
            user=settings['MYSQL_USER'],
            password=settings['MYSQL_PASSWORD'],
            port=settings['MYSQL_PORT'],
            database=settings['MYSQL_DBNAME'],
            charset=settings['MYSQL_CHARSET'],
            use_unicode=True,
            # 设置游标类型
            cursorclass=cursors.DictCursor
        )
        # 创建连接池
        db_pool = adbapi.ConnectionPool('pymysql', **db_params)

        # 返回一个pipeline对象
        return cls(db_pool)

    # 处理item函数
    def process_item(self, item, spider):
        # 对象拷贝，深拷贝  --- 这里是解决数据重复问题！！！
        asynItem = copy.deepcopy(item)

        # 把要执行的sql放入连接池
        query = self.db_pool.runInteraction(self.insert_into, asynItem)

        # 如果sql执行发送错误,自动回调addErrBack()函数
        query.addErrback(self.handle_error, item, spider)

        # 返回Item
        return item

    # 处理sql函数
    def insert_into(self, cursor, item):
        # 创建sql语句
        ''' sql = "INSERT INTO m_info (name,actor) VALUES ('{}','{}')".format(
            item['name'], item['actor']) '''
        # 执行sql语句
        # cursor.execute(sql)

        # 使用uuid去创建影片剧集id
        videoId = uuid.uuid1()
        print(type(videoId))
        print(len(str(videoId)))

        print("一级标题"+item['videoPrimaryClassification'])
        print("二级标题" + item['videoSecondaryClassification'])
        # 创建插入影片信息的sql
        video_sql = "INSERT INTO video_info (video_id,video_primary_classification,video_secondary_classification,video_name," \
                    "video_alias,video_director,video_protagonist,video_type,video_area,video_language,video_release_date," \
                    "video_update_date,video_plot_introduction,video_img_remote_url,video_score,video_detail_url) " \
                    "VALUES ('{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}')".format(
            videoId, item['videoPrimaryClassification'], item['videoSecondaryClassification'],
            item['videoName'], item['videoAlias'], item['videoDirector'], item['videoProtagonist'],
            item['videoType'], item['videoArea'], item['videoLanguage'], item['videoReleaseDate'],
            item['videoUpdateDate'], item['videoPlotIntroduction'], item['videoImgRemoteUrl'],
            item['videoScore'], item['videoDetailUrl'])
        # 执行插入影片信息sql
        cursor.execute(video_sql)
        # 创建插入影片资源信息的sql

        # 执行插入影片资源信息sql 循环插入资源数据
        for resourceLink in item['resourceLinks']:
            resourceId = uuid.uuid1()
            resource_sql = "INSERT INTO resource_info (resource_id,resource_source,resource_link,resource_video_id) " \
                           "VALUES ('{}','{}','{}','{}')".format(
                resourceId, item['resourceSource'], resourceLink, videoId)
            cursor.execute(resource_sql)

    # 错误函数
    def handle_error(self, failure, item, spider):
        # #输出错误信息
        print("failure", failure)

